import os
import sqlite3
import sys

from loguru import logger


def migrate(db_file_path: str):
    db_path = db_file_path
    if not os.path.exists(db_path):
        logger.error(f"Error: Database file not found at {db_path}")
        sys.exit(1)
    logger.info(f"003_add_table_wx_published_articles: Migrating database at {db_path}")
    conn = None
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        logger.debug(f"Successfully connected to SQLite database at {db_path}")
        # 检查表 db_version 中是否存在 db_ver 字段
        cursor.execute(
            "SELECT name FROM sqlite_master WHERE type='table' AND name='db_version'"
        )
        table_exists = cursor.fetchone()
        if not table_exists:
            logger.error("Table 'db_version' does not exist.")
            sys.exit(1)
        cursor.execute(
            "SELECT name FROM pragma_table_info('db_version') WHERE name='db_ver'"
        )
        column_exists = cursor.fetchone()
        if not column_exists:
            logger.error("Column 'db_ver' does not exist in table 'db_version'.")
            sys.exit(1)
        # 检查表 wx_published_articles 是否存在
        cursor.execute(
            "SELECT name FROM sqlite_master WHERE type='table' AND name='wx_published_articles'"
        )
        table_exists = cursor.fetchone()
        if not table_exists:
            logger.error("Table 'wx_published_articles' does not exist.")
            sys.exit(1)
        # 检查 db_ver 是否为 3
        cursor.execute("SELECT db_ver FROM db_version ORDER BY db_ver DESC LIMIT 1")
        row = cursor.fetchone()
        if not row:
            logger.error("No version record found in table 'db_version'.")
            sys.exit(1)

        if row[0] != 3:
            logger.error("Database version is already 3.")
            sys.exit(1)
    except sqlite3.Error as e:
        result = str(e)
        logger.error(f"SQLite database error: {result}")
        sys.exit(1)
    except Exception as e:
        result = str(e)
        logger.error(f"An unexpected error occurred: {result}")
        sys.exit(1)
    finally:
        if conn:
            conn.close()
            logger.debug("SQLite database connection closed.")


if __name__ == "__main__":
    # Check if root directory path is provided as a command-line argument
    if len(sys.argv) < 2:
        logger.info("Usage: python 003._add_xxxx.py <article_dir> <db_file_path>")
        sys.exit(1)
    db_file_path = sys.argv[2]
    migrate(db_file_path)
